【新機能】LOAD DATA文(Cross-cloud transfer)でS3のファイルをBigQueryテーブルへロードできるようになりました

【新機能】LOAD DATA文(Cross-cloud transfer)でS3のファイルをBigQueryテーブルへロードできるようになりました

BigQueryのLOAD DATA文(Cross-cloud transfer)がGAされた。 Amazon S3のファイルをBigQuery内のテーブルにロードしてみる。
Clock Icon2022.11.10

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
昨日、BigQueryLOAD DATA 文(Cross-cloud transfer)が GA となりました。

BigQuery release notes > November 09, 2022

Cross-cloud transfer from Amazon S3 】 ※執筆時点では日本語のガイドは未更新

もともと BigQuery Omni外部テーブルAmazon S3など別クラウドのストレージを参照することはできました。 ↓この辺は以前ブログにしています。

S3からBigQueryへ連携する方法いろいろ(Omni/Transfer Service)

※現在はストレージへのアクセスなどは BigLake の機能として整備されました。

ただし、S3の場合、その外部テーブルは aws-us-east-1 という特別なリージョンで作成する必要があり、 既にBigQuery内にある US など他のリージョンのデータと組み合わせて使うことが難しく、 また、別のリージョンにテーブルとして持ち出すこともできないようでした。

今回のLOAD DATA文を使うことで、S3にあるデータをBigQuery内の通常のテーブルとして格納することができるようになります。

以下ではS3からのロードを試してみます。

制限事項

GA直後ということもあるせいか、いろいろ 制限事項 があります。

例えばリージョンは、BigQueryの接続が aws-us-east-1 リージョンである必要があるため S3のリージョンも us-east-1 のみ、 宛先のBigQueryデータセットも USUS-EAST-4 の2つになるようです。(2022/11/10時点)

また、BigQuery Omniの外部テーブルが定額料金のみ( ※最近オンデマンド料金で試せるようになった )だったのに対し、 LOAD DATA文では予約スロットが使われないそうです。

Limitations

・LOAD DATA jobs don't run on reservations. Jobs utilize on-demand slots that are managed by Google Cloud.

使ってみる

とりあえず使ってみます。

外部テーブルの時と同じく、BigQueryの接続(S3)の作成が必要になります。

↓こんな感じで作成できますが、AWS側の権限付与などは、 先述の以前のブログ をご覧下さい。

app_name=bqomni-s3-access
aws_account_id=<AWSのアカウントID>
aws_iam_role_id="arn:aws:iam::${aws_account_id}:role/${app_name}-role"

bq mk --connection --connection_type='AWS' \
--iam_role_id=$aws_iam_role_id \
--location=aws-us-east-1 \
${app_name}-conn



データ格納先になるデータセットを作成します。

USUS-EAST-4 とのことなので、前者のリージョンを使用。

CREATE SCHEMA bqomni_s3_transfer
OPTIONS(
    location="US"
);

ここからが本題のロードです。
S3上のファイルは圧縮されたJSON(JSON Lines)です。

Other statements in Google Standard SQL > LOAD DATA statement

LOAD DATA INTOレコード追記と、 LOAD DATA OVERWRITEテーブルごと上書きができるようです。

テーブルのスキーマ情報は自動で検出してくれるそうなので、まずは未指定でロードしてみます。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://<S3バケット名>/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

オプションなど外部テーブルの時と同じような書き方ですね。

↓無事ロードされました!

これで本題は終わりなのですが、もう少しいろいろ試してみます。

次にスキーマ情報を定義してロードしてみます。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_with_schema (
    station_number INTEGER, year INTEGER, month INTEGER, day INTEGER,
    fog BOOLEAN, rain BOOLEAN, snow BOOLEAN, hail BOOLEAN, thunder BOOLEAN, tornado BOOLEAN
)
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://<S3バケット名>/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

↓こちらは指定通りにロードされました。


JSON型でロードしたい!

リリースノートに以下のような記載がありました。

Load semi-structured JSON source data into BigQuery without providing a schema by using JSON columns in the destination table.

スキーマ定義せず JSON型 でロードできると、フィールドの追加などに柔軟に対応できそうなので、良さそうです。

いろいろ試したのですが、 ↓のようなもともとのJSONファイル(オブジェクトが入れ子になっていないフラットなもの)ではうまくJSON型として格納することができず…

{"station_number":"110240","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
{"station_number":"670050","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
{"station_number":"949350","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}
…

↓のようなデータ(入れ子があるもの)に変更しました。

{data:{"station_number":"110240","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
{data:{"station_number":"670050","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
{data:{"station_number":"949350","year":"2000","month":"1","day":"1","fog":false,"rain":false,"snow":false,"hail":false,"thunder":false,"tornado":false}}
…

変更後のファイルは、↓のSQLでJSON型としてロードできました。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_json (
    data JSON
)
FROM FILES (
    format='JSON',
    compression='GZIP',
    uris=['s3://<S3バケット名>/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;


ちなみに上記SQLの2行目のカラム指定が無いと、 STRUCT型としてスキーマを検出して定義・ロードされました。

(この辺は別のやり方が分かったら追記します。たぶん。)


(2022/11/18追記)
これが正しい方法なのか確証はありませんが、 ↓のようにJSON Linesを1列のCSVとしてみなすことで、 前述の前者のような入れ子の無いファイルでもそのままJSON型としてロードできました。

LOAD DATA INTO bqomni_s3_transfer.sample_tbl_jsonl_as_csv (
    data JSON
)
FROM FILES (
    format='CSV',
    field_delimiter='\t',
    compression='GZIP',
    uris=['s3://<S3バケット名>/bq-s3-access/*']
)
WITH CONNECTION `aws-us-east-1.bqomni-s3-access-conn`;

おわりに

S3(やAzure Blob Storage)との連携の幅が広がるのではないでしょーか。

関連情報/参考にさせていただいたページ

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.